﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Reflection;
using ZD_Utility;
using ZD_DataModel;
using System.Text.RegularExpressions;


namespace ZD_DataProvider
{
    public class ZD_DataProviderBase : IDataProvider
    {
        #region 私有变量定义
        //SQL语句容器
        private StringBuilder SbSql = new StringBuilder();
        //当前操作实体对象
        private IDataModel CurrentModel = null;
        //当前操作实体类对应表名特性类
        private ZD_TabNameAttribute TabNameAttr = null;
        //Model属性键值对
        private Dictionary<string, object> Dictionary = new Dictionary<string, object>();
        //Model属性类型键值对
        private Dictionary<string, string> DictionaryType = new Dictionary<string, string>();
        //Model外键关联表键值对
        private Dictionary<string, IDataModel> DicReference = new Dictionary<string, IDataModel>();
        //SQL参数化操作参数集合
        private List<SqlParameter> ParaList = new List<SqlParameter>();
        //通用业务逻辑字段集合
        private List<string> BuniessColumnList = new List<string>() { "id", "column", "where", "orderby", "idlist", "pageindex", "pagesize" };
        //Where条件拼接舍弃属性
        private List<string> WhereColumnList = new List<string>() { "column", "where", "orderby", "pageindex", "pagesize" };
        private DataSet ds = null;
        #endregion

        #region 构造函数
        public ZD_DataProviderBase(IDataModel Model)
        {
            CurrentModel = Model;
            TabNameAttr = ZD_GetModelCache.GetTabNameAttr(Model);
            Dictionary = ZD_ReflectionUtility.MappingModelToKeyAndValue(Model, ref DictionaryType);
        }
        #endregion


        #region 通用CRUD

        #region 查询
        /// <summary>
        /// 返回实体泛型集合
        /// </summary>
        /// <typeparam name="IDataModel"></typeparam>
        /// <returns></returns>
        public List<T> GetList<T>() where T : IDataModel, new()
        {
            List<T> list = new List<T>();
            ds = GetDataSet();
            if (ds.Tables.Count > 0)
            {
                list = ZD_ReflectionUtility.MappingDataTableToList<T>(ds.Tables[0]);
            }
            return list;
        }

        /// <summary>
        /// 分页查询返回实体类泛型集合
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        public List<T> GetPageList<T>(ref int DataTotalCount) where T : IDataModel, new()
        {
            List<T> list = new List<T>();
            ds = GetPageDataSet(ref DataTotalCount);
            if (ds != null)
            {
                list = ZD_ReflectionUtility.MappingDataTableToList<T>(ds.Tables[0]);
            }
            return list;
        }

        /// <summary>
        /// 返回DataSet
        /// </summary>
        /// <param name="Model"></param>
        /// <returns></returns>
        public DataSet GetDataSet()
        {
            Dictionary["column"] = Dictionary["column"] == null ? "*" : Dictionary["column"];
            SbSql.AppendFormat(" select {0} from {1} {2} ", Dictionary["column"], TabNameAttr.TabName, WhereConditionParaBulider());
            if (Dictionary["orderby"] != null)
            {
                SbSql.AppendFormat(" order by {0} ", Dictionary["orderby"]);
            }
            else
            {
                SbSql.AppendFormat(" order by {0} Desc ", TabNameAttr.PrimaryKey);
            }
            ds = ZD_DbHelperSQL.InitDataAdapter(SbSql.ToString(), CommandType.Text, ParaList);
            return ds;
        }

        /// <summary>
        /// 分页查询返回DataSet
        /// </summary>
        /// <param name="Model"></param>
        /// <returns></returns>
        public DataSet GetPageDataSet(ref int DataTotalCount)
        {
            ParaList.Add(new SqlParameter("@TabName", TabNameAttr.TabName));
            ParaList.Add(new SqlParameter("@StrColumn", Dictionary["column"]));
            ParaList.Add(new SqlParameter("@CurrPage", Dictionary["pageindex"]));
            ParaList.Add(new SqlParameter("@PageSize", Dictionary["pagesize"]));
            ParaList.Add(new SqlParameter("@StrWhere", WhereConditionBulider(false)));
            ParaList.Add(new SqlParameter("@StrOrder", Dictionary["orderby"]));
            ds = ZD_DbHelperSQL.InitDataAdapter("GetPage", CommandType.StoredProcedure, ParaList);
            if (ds.Tables.Count > 1)
            {
                DataTotalCount = Convert.ToInt32(ds.Tables[1].Rows[0][0]);
            }
            else
            {
                DataTotalCount = 0;
                return null;
            }
            return ds;
        }

        /// <summary>
        /// 根据指定条件获取对应Model
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="Model"></param>
        /// <returns></returns>
        public T GetModel<T>()
            where T : IDataModel, new()
        {
            Dictionary["column"] = Dictionary["column"] == null ? "*" : Dictionary["column"];
            SbSql.AppendFormat("select {0} from {1} {2} ", Dictionary["column"], TabNameAttr.TabName, WhereConditionParaBulider());
            ds = ZD_DbHelperSQL.InitDataAdapter(SbSql.ToString(), CommandType.Text, ParaList);
            if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                return ZD_ReflectionUtility.MappingDataRowToModel<T>(ds.Tables[0].Rows[0]);
            }
            return new T();
        }
        #endregion

        #region 新增
        /// <summary>
        /// 新增记录
        /// </summary>
        /// <returns>返回新增后的实体，实体类Id属性为插入记录的Id（自动编号，IdentityId）</returns>
        public T InsertReturnModel<T>() where T : IDataModel, new()
        {
            InsertSQLBulider();
            ZD_DbHelperSQL.InitExecuteNonQuery(SbSql.ToString(), CommandType.Text, ParaList);
            return (T)CurrentModel;
        }

        /// <summary>
        /// 新增记录
        /// </summary>
        /// <returns>返回被影响行数</returns>
        public int InsertReturnAffectRows(IDataModel Model)
        {
            InsertSQLBulider();
            return ZD_DbHelperSQL.InitExecuteNonQuery(SbSql.ToString(), CommandType.Text, ParaList);
        }

        #region InsertSQLBulider
        /// <summary>
        /// 拼接生成参数化形式Insert操作语句
        /// </summary>
        /// <param name="Model"></param>
        /// <returns></returns>
        StringBuilder InsertSQLBulider()
        {
            StringBuilder SbValues = new StringBuilder(" values( ");
            SbSql.AppendFormat(" insert into {0} ", TabNameAttr.TabName);
            SbSql.Append("(");
            foreach (string item in Dictionary.Keys)
            {
                if (BuniessColumnList.Contains(item) || Dictionary[item] == null) { continue; }
                SbSql.AppendFormat("{0},", item);
                SbValues.AppendFormat("@{0},", item);
                ParaList.Add(new SqlParameter("@" + item, Dictionary[item]));
            }
            SbValues.Length--;
            SbSql.Length--;
            SbValues.Append(")");
            SbSql.Append(")");
            SbSql.Append(SbValues.ToString());
            return SbSql;
        }
        #endregion
        #endregion

        #region 删除

        /// <summary>
        ///  根据主键删除对应记录
        /// </summary>
        /// <returns>返回被影响行数</returns>
        public int DeleteReturnAffectRows()
        {
            SbSql.AppendFormat(" delete [{0}] {1} ", TabNameAttr.TabName, WhereConditionParaBulider());
            return ZD_DbHelperSQL.InitExecuteNonQuery(SbSql.ToString(), CommandType.Text, ParaList);
        }
        #endregion

        #region 修改
        /// <summary>
        ///  根据主键修改对应记录
        /// </summary>
        /// <returns>返回被影响行数</returns>
        public int UpdateReturnAffectRows()
        {
            UpdateSQLBulider();
            return ZD_DbHelperSQL.InitExecuteNonQuery(SbSql.ToString(), CommandType.Text, ParaList);
        }

        #region UpdateSQLBulider
        StringBuilder UpdateSQLBulider()
        {
            SbSql.AppendFormat(" update {0} set  ", TabNameAttr.TabName);
            foreach (string item in Dictionary.Keys)
            {
                if (BuniessColumnList.Contains(item) || Dictionary[item] == null) { continue; }
                SbSql.AppendFormat("{0}=@{0},", item);
                ParaList.Add(new SqlParameter("@" + item, Dictionary[item]));
            }
            SbSql.Length--;
            SbSql.AppendFormat(" where {0}={1}", TabNameAttr.PrimaryKey, Dictionary[TabNameAttr.PrimaryKey.ToLower()]);
            return SbSql;
        }
        #endregion
        #endregion

        #endregion

        #region 拼接Where条件

        /// <summary>
        /// 拼接Where条件
        /// </summary>
        /// <param name="NoWhereKeyword">是否需将where关键字，true为加，false为不加</param>
        /// <returns></returns>
        public string WhereConditionBulider(bool IsWhereKeyword)
        {
            StringBuilder whereCondition = new StringBuilder();
            StringBuilder whereFinal = new StringBuilder();
            foreach (string item in Dictionary.Keys)
            {
                if (Dictionary[item] == null)
                    continue;
                if (!WhereColumnList.Contains(item))
                {
                    if (item == "idlist")
                    {
                        whereCondition.AppendFormat(" {0} in ({1}) and ", TabNameAttr.PrimaryKey, Dictionary[item]);
                    }
                    else
                    {
                        switch (DictionaryType[item])
                        {
                            case "int":
                                whereCondition.AppendFormat(" {0}={1} and ", item, Dictionary[item]);
                                break;
                            case "string":
                                whereCondition.AppendFormat(" {0}='{1}' and ", item, Dictionary[item]);
                                break;
                            case "guid":
                                whereCondition.AppendFormat(" {0}='{1}' and ", item, Dictionary[item]);
                                break;
                            default:
                                break;
                        }
                    }
                }
            }
            if (whereCondition.Length > 0)
            {
                whereCondition.Length = whereCondition.Length - 4;
            }
            if (Dictionary["where"] != null)
            {
                if (IsWhereKeyword)
                {
                    whereFinal.Append(" where ");
                }
                whereFinal.AppendFormat(" {0}", Dictionary["where"]);
                if (whereCondition.Length > 0)
                {
                    whereFinal.AppendFormat("and {0} ", whereCondition);
                }
            }
            else if (whereCondition.Length > 0)
            {
                if (IsWhereKeyword)
                {
                    whereFinal.Append(" where ");
                }
                whereFinal.AppendFormat(" {0} ", whereCondition);
            }
            return whereFinal.ToString();
        }

        /// <summary>
        /// 拼接Where条件
        /// </summary>
        /// <returns></returns>
        public string WhereConditionBulider()
        {
            return WhereConditionBulider(true);
        }

        /// <summary>
        /// 拼接Where条件（参数化方式）
        /// </summary>
        /// <param name="IsWhereKeyword"></param>
        /// <returns></returns>
        public string WhereConditionParaBulider(bool IsWhereKeyword)
        {
            StringBuilder whereCondition = new StringBuilder();
            StringBuilder whereFinal = new StringBuilder();
            foreach (string item in Dictionary.Keys)
            {
                if (Dictionary[item] == null)
                    continue;
                if (!WhereColumnList.Contains(item))
                {
                    if (item == "idlist")
                    {
                        whereCondition.AppendFormat(" {0} in ({1}) and ", TabNameAttr.PrimaryKey, Dictionary[item]);
                    }
                    else
                    {
                        whereCondition.AppendFormat(" {0}=@{0} and ", item);
                        ParaList.Add(new SqlParameter("@" + item, Dictionary[item]));
                    }
                }
            }
            if (whereCondition.Length > 0)
            {
                whereCondition.Length = whereCondition.Length - 4;
            }
            if (Dictionary["where"] != null)
            {
                if (IsWhereKeyword)
                {
                    whereFinal.Append(" where ");
                }
                whereFinal.AppendFormat(" {0}", Dictionary["where"]);
                if (whereCondition.Length > 0)
                {
                    whereFinal.AppendFormat("and {0} ", whereCondition);
                }
            }
            else if (whereCondition.Length > 0)
            {
                if (IsWhereKeyword)
                {
                    whereFinal.Append(" where ");
                }
                whereFinal.AppendFormat(" {0} ", whereCondition);
            }
            return whereFinal.ToString();
        }

        /// <summary>
        /// 拼接Where条件，不加where关键字（参数化方式）
        /// </summary>
        /// <returns></returns>
        public string WhereConditionParaBulider()
        {
            return WhereConditionParaBulider(true);
        }
        #endregion
    }
}
